class Sideffect < ActiveRecord::Base
  has_and_belongs_to_many :ratings
  include SideffectHelper
  def self.top(count)
    sql = <<-SQL
      SELECT s.*, t.cnt2 cnt
      FROM sideffects s,
      (
        SELECT rs.sideffect_id id2, count(distinct rs.rating_id) cnt2
        FROM ratings_sideffects rs
        GROUP BY rs.sideffect_id
        ORDER BY cnt2 DESC
        LIMIT ?
      ) t
      WHERE s.id = t.id2 
      ORDER BY cnt DESC, s.name ASC
    SQL
    find_by_sql([sql, count])
  end

  def drugs_by_category(category, filter_options = {})
    sql = <<-SQL
      SELECT d.*, count(distinct r.id) cnt
      FROM drugs d, ratings r, categories_drugs cd, ratings_sideffects rs
      WHERE d.id = r.drug_id
      AND d.id = cd.drug_id
      AND rs.rating_id = r.id
      AND #{filter_sql(filter_options)}
      AND rs.sideffect_id = ?
      AND cd.category_id = ?
      GROUP BY d.id
      ORDER BY cnt DESC, d.name ASC
    SQL
    Drug.find_by_sql([sql, self[:id], category.id])
  end
  
  def ratings_number_by_category(category, filter_options = {})
    sql = <<-SQL
      SELECT count(distinct r.id) cnt
      FROM ratings r, categories_drugs cd, ratings_sideffects rs
      WHERE rs.sideffect_id = ?
      AND rs.rating_id = r.id
      AND r.drug_id = cd.drug_id
      AND cd.category_id = ?
      AND #{filter_sql(filter_options)}
    SQL
    self.class.count_by_sql([sql, self[:id], category.id])
  end
end
